package com.css.fxfzysx.modules.vaAnalysis.repository.impl;

import com.css.fxfzysx.constants.RelationConstants;
import com.css.fxfzysx.modules.cztDataMange.vo.QueryBacInformationVo;
import com.css.fxfzysx.modules.vaAnalysis.repository.CsVulnerabilityRepository;
import com.css.fxfzysx.modules.vaAnalysis.vo.GroupVo;
import com.css.fxfzysx.modules.vaolEngineering.entity.MtolProjectEntity;
import com.css.fxfzysx.modules.vaolEngineering.utils.MainTableUtils;
import com.css.fxfzysx.modules.vaomeFacilities.entity.CipVulnerabilityEntity;
import com.css.fxfzysx.modules.vaomeFacilities.entity.CipvMatrixEntity;
import com.css.fxfzysx.util.PlatformObjectUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

@Repository
public class CsVulnerabilityRepositoryImpl implements CsVulnerabilityRepository {

    @Resource
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private MainTableUtils mainTableUtils;

    @Override
    public HashMap<String, Object> buildingCsVulnerability(QueryBacInformationVo vo) {
        StringBuilder yearCount = new StringBuilder("SELECT years, count(years) yearsCount FROM( SELECT cbvi_id AS ID, NUMBER, province, city, county, structure_type, structure_type1, years, fortification, create_user, create_time, del_flag, use0, use1, user_name FROM "+ RelationConstants.getConstant(vo.getProvince()) +" UNION ALL SELECT cbcpi_id AS ID, NUMBER, province, city, county, structure_type, extends1 AS structure_type1, years, fortification, create_user, create_time, del_flag, purpose AS use0, purpose1 AS use1, user_name FROM czt_brick_concrete_param_info UNION ALL SELECT cal_id AS ID, NUMBER, province, city, county, structure_type, structure_type1, years, fortification, create_user_id AS create_user, create_time, del_flag, purpose AS use0, purpose1 AS use1, create_user_name AS user_name FROM czt_crs_calentirety_result UNION ALL SELECT chbpi_id AS ID, NUMBER, province, city, county, structure_type, structure_type1, years, fortification, create_user, create_time, del_flag, purpose AS use0, purpose1 AS use1, user_name FROM czt_high_building_param_info UNION ALL SELECT cpvi_id AS ID, NUMBER, province, city, county, structure_type0 AS structure_type, structure_type1, years, fortification, create_user, create_time, del_flag, purpose0 AS use0, purpose1 AS use1, user_name FROM czt_probability_vulnerability_info) AS DATA WHERE del_flag = '0'");
        StringBuilder stCount = new StringBuilder("SELECT structure_type, count(structure_type) structureTypeCount FROM( SELECT cbvi_id AS ID, NUMBER, province, city, county, structure_type, structure_type1, years, fortification, create_user, create_time, del_flag, use0, use1, user_name FROM "+ RelationConstants.getConstant(vo.getProvince()) +" UNION ALL SELECT cbcpi_id AS ID, NUMBER, province, city, county, structure_type, extends1 AS structure_type1, years, fortification, create_user, create_time, del_flag, purpose AS use0, purpose1 AS use1, user_name FROM czt_brick_concrete_param_info UNION ALL SELECT cal_id AS ID, NUMBER, province, city, county, structure_type, structure_type1, years, fortification, create_user_id AS create_user, create_time, del_flag, purpose AS use0, purpose1 AS use1, create_user_name AS user_name FROM czt_crs_calentirety_result UNION ALL SELECT chbpi_id AS ID, NUMBER, province, city, county, structure_type, structure_type1, years, fortification, create_user, create_time, del_flag, purpose AS use0, purpose1 AS use1, user_name FROM czt_high_building_param_info UNION ALL SELECT cpvi_id AS ID, NUMBER, province, city, county, structure_type0 AS structure_type, structure_type1, years, fortification, create_user, create_time, del_flag, purpose0 AS use0, purpose1 AS use1, user_name FROM czt_probability_vulnerability_info) AS DATA WHERE del_flag = '0' ");
        StringBuilder fortificationCount = new StringBuilder("SELECT fortification, count(fortification) fortificationCount  FROM( SELECT cbvi_id AS ID, NUMBER, province, city, county, structure_type, structure_type1, years, fortification, create_user, create_time, del_flag, use0, use1, user_name FROM "+ RelationConstants.getConstant(vo.getProvince()) +" UNION ALL SELECT cbcpi_id AS ID, NUMBER, province, city, county, structure_type, extends1 AS structure_type1, years, fortification, create_user, create_time, del_flag, purpose AS use0, purpose1 AS use1, user_name FROM czt_brick_concrete_param_info UNION ALL SELECT cal_id AS ID, NUMBER, province, city, county, structure_type, structure_type1, years, fortification, create_user_id AS create_user, create_time, del_flag, purpose AS use0, purpose1 AS use1, create_user_name AS user_name FROM czt_crs_calentirety_result UNION ALL SELECT chbpi_id AS ID, NUMBER, province, city, county, structure_type, structure_type1, years, fortification, create_user, create_time, del_flag, purpose AS use0, purpose1 AS use1, user_name FROM czt_high_building_param_info UNION ALL SELECT cpvi_id AS ID, NUMBER, province, city, county, structure_type0 AS structure_type, structure_type1, years, fortification, create_user, create_time, del_flag, purpose0 AS use0, purpose1 AS use1, user_name FROM czt_probability_vulnerability_info) AS DATA WHERE del_flag = '0' ");
        StringBuilder useCount = new StringBuilder("SELECT use0, count(use0) use0Count FROM( SELECT cbvi_id AS ID, NUMBER, province, city, county, structure_type, structure_type1, years, fortification, create_user, create_time, del_flag, use0, use1, user_name FROM "+ RelationConstants.getConstant(vo.getProvince()) +" UNION ALL SELECT cbcpi_id AS ID, NUMBER, province, city, county, structure_type, extends1 AS structure_type1, years, fortification, create_user, create_time, del_flag, purpose AS use0, purpose1 AS use1, user_name FROM czt_brick_concrete_param_info UNION ALL SELECT cal_id AS ID, NUMBER, province, city, county, structure_type, structure_type1, years, fortification, create_user_id AS create_user, create_time, del_flag, purpose AS use0, purpose1 AS use1, create_user_name AS user_name FROM czt_crs_calentirety_result UNION ALL SELECT chbpi_id AS ID, NUMBER, province, city, county, structure_type, structure_type1, years, fortification, create_user, create_time, del_flag, purpose AS use0, purpose1 AS use1, user_name FROM czt_high_building_param_info UNION ALL SELECT cpvi_id AS ID, NUMBER, province, city, county, structure_type0 AS structure_type, structure_type1, years, fortification, create_user, create_time, del_flag, purpose0 AS use0, purpose1 AS use1, user_name FROM czt_probability_vulnerability_info) AS DATA WHERE del_flag = '0' ");
        if (!PlatformObjectUtils.isEmpty(vo.getProvince())) {
            yearCount.append(" and province ='" + vo.getProvince() + "'");
            stCount.append(" and province ='" + vo.getProvince() + "'");
            fortificationCount.append(" and province ='" + vo.getProvince() + "'");
            useCount.append(" and province ='" + vo.getProvince() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(vo.getCity())) {
            yearCount.append(" and city ='" + vo.getCity() + "'");
            stCount.append(" and city ='" + vo.getCity() + "'");
            fortificationCount.append(" and city ='" + vo.getCity() + "'");
            useCount.append(" and city ='" + vo.getCity() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(vo.getCounty())) {
            yearCount.append(" and county ='" + vo.getCounty() + "'");
            stCount.append(" and county ='" + vo.getCounty() + "'");
            fortificationCount.append(" and county ='" + vo.getCounty() + "'");
            useCount.append(" and county ='" + vo.getCounty() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(vo.getYears())) {
            yearCount.append(" and years ='" + vo.getYears() + "'");
            stCount.append(" and years ='" + vo.getYears() + "'");
            fortificationCount.append(" and years ='" + vo.getYears() + "'");
            useCount.append(" and years ='" + vo.getYears() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(vo.getCreateStartTime())) {
            yearCount.append(" and create_time >= '" + vo.getCreateStartTime() + "'");
            stCount.append(" and create_time >= '" + vo.getCreateStartTime() + "'");
            fortificationCount.append(" and create_time >= '" + vo.getCreateStartTime() + "'");
            useCount.append(" and create_time >= '" + vo.getCreateStartTime() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(vo.getCreateEndTime())) {
            String endTime = vo.getCreateEndTime() + " 23:59:59";
            yearCount.append(" and create_time <='" + endTime + "'");
            stCount.append(" and create_time <='" + endTime + "'");
            fortificationCount.append(" and create_time <='" + endTime + "'");
            useCount.append(" and create_time <='" + endTime + "'");
        }
        if (!PlatformObjectUtils.isEmpty(vo.getStructureType())) {
            yearCount.append(" and structure_type ='" + vo.getStructureType() + "'");
            stCount.append(" and structure_type ='" + vo.getStructureType() + "'");
            fortificationCount.append(" and structure_type ='" + vo.getStructureType() + "'");
            useCount.append(" and structure_type ='" + vo.getStructureType() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(vo.getFortification())) {
            yearCount.append(" and fortification ='" + vo.getFortification() + "'");
            stCount.append(" and fortification ='" + vo.getFortification() + "'");
            fortificationCount.append(" and fortification ='" + vo.getFortification() + "'");
            useCount.append(" and fortification ='" + vo.getFortification() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(vo.getUserName())) {
            yearCount.append(" and user_name like '%" + vo.getUserName() + "%'");
            stCount.append(" and user_name like '%" + vo.getUserName() + "%'");
            fortificationCount.append(" and user_name like '%" + vo.getUserName() + "%'");
            useCount.append(" and user_name like '%" + vo.getUserName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(vo.getUse0())) {
            yearCount.append(" and use0 ='" + vo.getUse0() + "'");
            stCount.append(" and use0 ='" + vo.getUse0() + "'");
            fortificationCount.append(" and use0 ='" + vo.getUse0() + "'");
            useCount.append(" and use0 ='" + vo.getUse0() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(vo.getUse1())) {
            yearCount.append(" and use1 ='" + vo.getUse1() + "'");
            stCount.append(" and use1 ='" + vo.getUse1() + "'");
            fortificationCount.append(" and use1 ='" + vo.getUse1() + "'");
            useCount.append(" and use1 ='" + vo.getUse1() + "'");
        }
        yearCount.append(" group by years");
        stCount.append(" group by structure_type");
        fortificationCount.append(" group by fortification");
        useCount.append(" group by use0");

        List<GroupVo> yearList = jdbcTemplate.query(yearCount.toString(), new BeanPropertyRowMapper<>(GroupVo.class));
        List<GroupVo> stList = jdbcTemplate.query(stCount.toString(), new BeanPropertyRowMapper<>(GroupVo.class));
        List<GroupVo> fortificationList = jdbcTemplate.query(fortificationCount.toString(), new BeanPropertyRowMapper<>(GroupVo.class));
        List<GroupVo> useList = jdbcTemplate.query(useCount.toString(), new BeanPropertyRowMapper<>(GroupVo.class));

        HashMap<String, Object> resultMap = new HashMap<>();

        HashMap<String, Object> yearsMap = new HashMap<>();
        yearList.forEach(it->{
            yearsMap.put(it.getYears(),it.getYearsCount());
        });
        resultMap.put("years",yearsMap);

        HashMap<String, Object> structureTypeMap = new HashMap<>();
        stList.forEach(it->{
            structureTypeMap.put(it.getStructureType(),it.getStructureTypeCount());
        });
        resultMap.put("structureType",structureTypeMap);

        HashMap<String, Object> fortificationMap = new HashMap<>();
        fortificationList.forEach(it->{
            fortificationMap.put(it.getFortification(),it.getFortificationCount());
        });
        resultMap.put("fortification",fortificationMap);

        HashMap<String, Object> useMap = new HashMap<>();
        useList.forEach(it->{
            useMap.put(it.getUse0(),it.getUse0Count());
        });
        resultMap.put("use",useMap);
        return resultMap;
    }

    @Override
    public HashMap<String, Object> lifelineCsVulnerability(MtolProjectEntity mtolProjectEntity) {
        StringBuilder businessObjectSql = new StringBuilder("select business_object, count(business_object) business_object_count from czt_smx_main_table where del_flag = '0' and type is not null ");
        StringBuilder yearSql = new StringBuilder("select CASE WHEN YEAR < 1978 THEN '1978年以前' WHEN YEAR BETWEEN 1978 AND 1988 THEN '1978-1989年' WHEN YEAR BETWEEN 1989 AND 2000 THEN '1989-2001年' WHEN YEAR > 2000 THEN '2001年以后' END AS YEAR, count(year) year_count from czt_smx_main_table where del_flag = '0' and type is not null ");
        StringBuilder fortificationSql = new StringBuilder("select type,fortification, count(fortification) fortification_count from czt_smx_main_table where del_flag = '0' and type is not null ");

        if (!PlatformObjectUtils.isEmpty(mtolProjectEntity.getBusinessObject())) {
            businessObjectSql.append(" and business_object ='" + mtolProjectEntity.getBusinessObject() + "'");
            yearSql.append(" and business_object ='" + mtolProjectEntity.getBusinessObject() + "'");
            fortificationSql.append(" and business_object ='" + mtolProjectEntity.getBusinessObject() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(mtolProjectEntity.getUserName())) {
            businessObjectSql.append(" and user_name like '%" + mtolProjectEntity.getUserName() + "%'");
            yearSql.append(" and user_name like '%" + mtolProjectEntity.getUserName() + "%'");
            fortificationSql.append(" and user_name like '%" + mtolProjectEntity.getUserName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(mtolProjectEntity.getYears())) {
            if(mtolProjectEntity.getYears() == 1){
                businessObjectSql.append(" and year < 1978");
                yearSql.append(" and year < 1978");
                fortificationSql.append(" and year < 1978");
            }else if(mtolProjectEntity.getYears() == 2){
                businessObjectSql.append(" and year between 1978 and 1989");
                yearSql.append(" and year between 1978 and 1989");
                fortificationSql.append(" and year between 1978 and 1989");
            }else if(mtolProjectEntity.getYears() == 3){
                businessObjectSql.append(" and year between 1989 and 2001");
                yearSql.append(" and year between 1989 and 2001");
                fortificationSql.append(" and year between 1989 and 2001");
            }else if(mtolProjectEntity.getYears() == 4){
                businessObjectSql.append(" and year > 2001");
                yearSql.append(" and year > 2001");
                fortificationSql.append(" and year > 2001");
            }
        }
        if (!PlatformObjectUtils.isEmpty(mtolProjectEntity.getCreateStartTime())) {
            businessObjectSql.append(" and create_time >= '" + mtolProjectEntity.getCreateStartTime() + "'");
            yearSql.append(" and create_time >= '" + mtolProjectEntity.getCreateStartTime() + "'");
            fortificationSql.append(" and create_time >= '" + mtolProjectEntity.getCreateStartTime() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(mtolProjectEntity.getCreateEndTime())) {
            String endTime = mtolProjectEntity.getCreateEndTime() + " 23:59:59";
            businessObjectSql.append(" and create_time <='" + endTime+"'");
            yearSql.append(" and create_time <='" + endTime+"'");
            fortificationSql.append(" and create_time <='" + endTime+"'");
        }
        if (!PlatformObjectUtils.isEmpty(mtolProjectEntity.getFortification())) {
            businessObjectSql.append(" and fortification ='" + mtolProjectEntity.getFortification() + "'");
            yearSql.append(" and fortification ='" + mtolProjectEntity.getFortification() + "'");
            fortificationSql.append(" and fortification ='" + mtolProjectEntity.getFortification() + "'");
        }

        businessObjectSql.append(" group by business_object");
        yearSql.append(" group by year");
        fortificationSql.append(" group by fortification,type");

        List<GroupVo> businessObjectList = jdbcTemplate.query(businessObjectSql.toString(), new BeanPropertyRowMapper<>(GroupVo.class));
        List<GroupVo> yearList = jdbcTemplate.query(yearSql.toString(), new BeanPropertyRowMapper<>(GroupVo.class));
        List<GroupVo> fortificationList = jdbcTemplate.query(fortificationSql.toString(), new BeanPropertyRowMapper<>(GroupVo.class));

        HashMap<String, Object> map = new HashMap<>();
        HashMap<String, Object> businessObjectMap = new HashMap<>();
        HashMap<String, Object> yearMap = new HashMap<>();
        HashMap<String, Object> fortificationMap = new HashMap<>();

        businessObjectList.forEach(it->{
            businessObjectMap.put(it.getBusinessObject(),it.getBusinessObjectCount());
        });

        yearList.forEach(it->{
            yearMap.put(it.getYear(),it.getYearCount());
        });

        fortificationList.forEach(it->{
            if(it.getType() == 5){
                fortificationMap.put(mainTableUtils.getValueByKey("smxdlsfqk",it.getFortification()),it.getFortificationCount());
            }else {
                fortificationMap.put(mainTableUtils.getValueByKey("smxsfqk",it.getFortification()),it.getFortificationCount());
            }
        });

        map.put("fortification",fortificationMap);
        map.put("years",yearMap);
        map.put("mtolProject",businessObjectMap);
        return map;
    }

    @Override
    public HashMap<String, Object> MajorProjectsCsVulnerability(CipvMatrixEntity cipvMatrixEntity) {
        StringBuilder sql = new StringBuilder("select * from czt_important_project_vulner_matrix n where exists( select 1 from czt_important_project_vulner_matrix where del_flag='0'");
        if(PlatformObjectUtils.isNotEmpty(cipvMatrixEntity.getMajorProjectCategory())){
            if(cipvMatrixEntity.getMajorProjectCategory().equals("输油/气管线")){
                sql.append(" and major_project_category in ('输油管线','输气管线')");
            }else {
                sql.append(" and major_project_category ='" + cipvMatrixEntity.getMajorProjectCategory() + "'");
            }
        }
        if(PlatformObjectUtils.isNotEmpty(cipvMatrixEntity.getFortification())){
            sql.append(" and fortification ='" + cipvMatrixEntity.getFortification() + "'");
        }
        if(PlatformObjectUtils.isNotEmpty(cipvMatrixEntity.getYears())){
            sql.append(" and years ='" + cipvMatrixEntity.getYears() + "'");
        }
        if(PlatformObjectUtils.isNotEmpty(cipvMatrixEntity.getCipvType())){
            sql.append(" and cipv_type ='" + cipvMatrixEntity.getCipvType() + "'");
        }
        StringBuilder orderSql = new StringBuilder(" group by cipv_code having max(cipv_id) =n.cipv_id) order by create_time desc");
        sql.append(orderSql);
        List<CipVulnerabilityEntity> mtoList = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(CipVulnerabilityEntity.class));
        HashMap<String, Object> resultMap = new HashMap<>();
        HashMap<String, Object> majorProjectCategoryMap = new HashMap<>();
        Map<String, List<CipVulnerabilityEntity>> collect = mtoList.stream().collect(Collectors.groupingBy(CipVulnerabilityEntity::getMajorProjectCategory));
        ArrayList<CipVulnerabilityEntity> list = new ArrayList<>();
        collect.forEach((k,v)->{
            if(k.equals("输油管线") || k.equals("输气管线")) {
                list.addAll(v);
                majorProjectCategoryMap.put("输油/气管线", list.size());
            }else {
                majorProjectCategoryMap.put(k,v.size());
            }
        });
        resultMap.put("majorProjectCategory",majorProjectCategoryMap);

        HashMap<String, Object> fortificationMap = new HashMap<>();
        Map<String, List<CipVulnerabilityEntity>> collect1 = mtoList.stream().collect(Collectors.groupingBy(CipVulnerabilityEntity::getFortification));
        collect1.forEach((k,v)->{
            fortificationMap.put(k,v.size());
        });
        resultMap.put("fortification",fortificationMap);

        HashMap<String, Object> yearsMap = new HashMap<>();
        Map<String, List<CipVulnerabilityEntity>> collect2 = mtoList.stream().collect(Collectors.groupingBy(CipVulnerabilityEntity::getYears));
        collect2.forEach((k,v)->{
            yearsMap.put(k,v.size());
        });
        resultMap.put("years",yearsMap);
        return resultMap;
    }
}
